package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.ISupplierDao;
import com.weixin.entity.Supplier;
import com.weixin.util.C3p0Util;

public class SupplierDaoImpl implements ISupplierDao {
	C3p0Util c3p0=new C3p0Util();
	
	@Override
	public List<Supplier> selectAll() throws SQLException {
		String sql="select * from supplier_tb where is_show=1";
		return c3p0.getqr().query(sql, new BeanListHandler<Supplier>(Supplier.class));
	}

	@Override
	public Supplier selectById(int supplier_id) throws SQLException {
		String sql="select * from supplier_tb where is_show=1 and supplier_id=?";
		return c3p0.getqr().query(sql, new BeanHandler<Supplier>(Supplier.class),supplier_id);
	}

	@Override
	public boolean addSupplier(Supplier supplier) throws SQLException {
		String sql="insert into supplier_tb(supplier_name,supplier_address,supplier_,telephone,main_business,remark) values(?,?,?,?,?,?)";
		int i=c3p0.getqr().update(sql,supplier.getSupplier_name(),supplier.getSupplier_address(),supplier.getSupplier_(),supplier.getTelephone(),supplier.getMain_business(),supplier.getRemark());
		return i>0;
	}

	@Override
	public boolean editSupplier(Supplier supplier) throws SQLException {
		String sql="update supplier_tb set supplier_name=?,supplier_address=?,supplier_=?,telephone=?,main_business=?,remark=? where supplier_id=? ";
		int i=c3p0.getqr().update(sql,supplier.getSupplier_name(),supplier.getSupplier_address(),supplier.getSupplier_(),supplier.getTelephone(),supplier.getMain_business(),supplier.getRemark(),supplier.getSupplier_id());
		return i>0;
	}
	@Override
	public boolean moveById(int supplier_id) throws SQLException {
		String sql="update supplier_tb set is_show=0 where supplier_id=?";
		int i=c3p0.getqr().update(sql, supplier_id);
		return i>0;
	}

	@Override
	public List<Supplier> selectAllByPagination(int pageSize, int pageNum) throws SQLException {
		String sql="select * from supplier_tb where is_show=1 limit ?,?";
		return c3p0.getqr().query(sql, new BeanListHandler<Supplier>(Supplier.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int getAllTotal() throws SQLException {
		String sql="select count(*) from supplier_tb where is_show=1";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public List<Supplier> getForSearch(String search) throws SQLException {
		String sql="select * from supplier_tb where is_show=1 AND\r\n" + 
				"(supplier_id like ? or supplier_name like ? or supplier_address like ? or supplier_ LIKE ?\r\n" + 
				"or telephone like ? or main_business like ? or remark LIKE ?)";
		return c3p0.getqr().query(sql, new BeanListHandler<Supplier>(Supplier.class),"%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%");
	}

}
